import java.sql.*;

public class DBTest {
    // JDBC连接参数
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://154.201.92.66:3377/cake?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    static {
        try {
            // 加载MySQL驱动
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("驱动加载失败！");
        }
    }

    /**
     * 查询
     */
    private static void select() throws SQLException {
        // 连接数据库
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

        // 执行sql预处理对象
        String sql = "select `user`,`password` from `user`";
        PreparedStatement ps = conn.prepareStatement(sql);

        // 执行查询，遍历结果集
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String user = rs.getString("user");
            String password = rs.getString("password");
            System.out.println("用户名：" + user);
            System.out.println("密码：" + password);
            System.out.println();
        }

        // 释放资源
        conn.close();
    }

    /**
     * 删除
     */
    private static void delete() throws SQLException {
        // 连接数据库
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

        // 执行sql预处理对象
        String sql = "delete from `user` where `id` = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 10);
        ps.execute();

        // 释放资源
        conn.close();
    }

    /**
     * 修改
     */
    private static void update() throws SQLException {
        // 连接数据库
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

        // 执行sql预处理对象
        String sql = "update `user` set `password` = ? where `id` = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "123.com");
        ps.setInt(2, 10);
        ps.execute();

        // 释放资源
        conn.close();
    }

    /**
     * 新增
     */
    private static void insert() throws SQLException {
        // 连接数据库
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

        // 执行sql预处理对象
        String sql = "insert `user`(`user`,`password`,`name`) value(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "hua");
        ps.setString(2, "hua.com");
        ps.setString(3, "花李先");
        ps.execute();

        // 释放资源
        conn.close();
    }

    /**
     * 数据库连接测试
     */
    private static void conn() throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        if (conn != null) {
            System.out.println("数据库连接成功！");
        } else {
            System.out.println("数据库连接失败！");
        }
        conn.close();
    }

    public static void main(String[] args) throws SQLException {
        conn();
        // insert();
        // update();
        // delete();
        select();
        // MVC（原生：难以理解） SpringMVC SpringBoot
    }

}
